Basic Functions

Introduction

SQL provides a variety of built-in functions to perform calculations on data, modify individual data items, and manipulate outputs. These functions are categorized into aggregate functions and scalar functions. This section will cover the most commonly used basic functions.

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MAX, and MIN.

COUNT

The COUNT function returns the number of rows that match a specified condition.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example Count the number of employees in department 101:

SELECT COUNT(*)
FROM employees
WHERE departmentid = 101;

SUM

The SUM function returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example Calculate the total salary of all employees:

SELECT SUM(salary)
FROM employees;

AVG

The AVG function returns the average value of a numeric column.

SELECT AVG(columnname)
FROM table_name
WHERE condition;

Example Calculate the average salary of employees in department 101:

SELECT AVG(salary)
FROM employees
WHERE departmentid = 101;

MAX

The MAX function returns the highest value in a numeric column.

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example Find the highest salary among all employees:

SELECT MAX(salary)
FROM employees;

MIN

The MIN function returns the lowest value in a numeric column.

sql

SELECT MIN(column_name)
FROM table_name
WHERE condition;

Example Find the lowest salary among all employees:

SELECT MIN(salary)
FROM employees;

Scalar Functions

Scalar functions return a single value based on the input value. Common scalar functions include UPPER, LOWER, LENGTH, ROUND, and NOW.

UPPER

The UPPER function converts a string to uppercase.

SELECT UPPER(column_name)
FROM table_name;

Example Convert employee first names to uppercase:

SELECT UPPER(firstname)
FROM employees;

LOWER

The LOWER function converts a string to lowercase.

SELECT LOWER(column_name)
FROM table_name;

Example Convert employee last names to lowercase:

SELECT LOWER(lastname)
FROM employees;

LENGTH

The LENGTH function returns the length of a string.

SELECT LENGTH(column_name)
FROM table_name;

Example Find the length of employee first names:

SELECT LENGTH(firstname)
FROM employees;

ROUND

The ROUND function rounds a numeric value to the specified number of decimal places.

SELECT ROUND(column_name, decimals)
FROM table_name;

Example Round employee salaries to the nearest thousand:

SELECT ROUND(salary, -3)
FROM employees;

NOW

The NOW function returns the current date and time.

SELECT NOW();

Example Get the current date and time:

SELECT NOW();

Practice Exercises

• Select a count of all employees
• Find the sum of all product prices
• Get the average 'UnitPrice' or orders
• Find the maximum length of all empolyees firstname where the surname starts with s

---